package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;
import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.DqtzxiDao;

/**
 * 
 * @author qjy
 * @version 创建时间：2016年06月14日 上午13:25:42
 * @功能：查询异常信息
 */

@Repository
public class DqtzxiDaoImpl  extends P2PJdbcDao implements DqtzxiDao{
	private Logger logger = Logger.getLogger(DqtzxiDaoImpl.class);
	/* 
	 * @方法名  XiList 
	 * @功能   TODO 获取点券来源列表
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.DqtzxiDao#XiList(net.sf.json.JSONObject)  
	 */
	public List<Map<String, Object>> XiList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String yhm=params.optString("yhm");
		String sjhm=params.optString("sjhm");
		String ltype=params.optString("ltype");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" select ID,USER_NAME,SJHM,LY_TYPE,LY_TJ_USER_NAME,DQ_JE,NOTE,LY_TJ_TZRXXID,LY_TJ_TZRXX_LY_TYPE,date_format(LY_DATE,'%Y-%m-%d %H:%i:%s') LY_DATE");
		sql.append(" from t_zhfw_tg_dq");
		sql.append(" where 1=1  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(LY_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(LY_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(yhm)){
			sql.append(" AND USER_NAME like "+ "'%"+yhm+"%'");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(ltype)){
			sql.append(" and LY_TYPE = :ltype ");
			paramMap.put("ltype", ltype);
		}
		if(0 != limit){
			sql.append(" ORDER BY LY_DATE DESC LIMIT :START,:LIMIT  ");
			paramMap.put("START", start);
			paramMap.put("LIMIT", limit);
		}
		logger.info("queryXiList sql is " + sql.toString());
		logger.info("queryXiList paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);

	}
	
	/* 
	 * @方法名  queryDqtzxx 
	 * @功能   TODO 查询投资获取点券的投资信息
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.DqtzxiDao#queryDqtzxx(net.sf.json.JSONObject)  
	 */
	public Map<String, Object> queryDqtzxx(JSONObject params) {
		String tzrxxid=params.optString("tzrxxid");
		String tzly=params.optString("tzly");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("tzrxxid", tzrxxid);
		paramMap.put("tzly", tzly);
		if (StringUtils.contains("01", tzly)) {//债权/散标投资
			sql.append("select ID, USERID, ZQID REF_XM_ORDER, TCJE TZJE, TZSJ, TCZT TZZT, HKZT, JXRQ, TZSY, HTBH from t_zhfw_tzrxx");
		} else if(StringUtils.contains("02", tzly)){//计划内投资
			sql.append("select ID, USERID, REF_XM_ORDER, TZJE, TZSJ, TZZT, HKZT, JXRQ, TZSY, HTBH from t_zhfw_jhn_tzrxx");
		}
		sql.append(" where ID=:tzrxxid");
		logger.info("queryDqtzxx sql is " + sql.toString());
		logger.info("queryDqtzxx paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}
	/* 
	 * @方法名  gettDqtzxiCount 
	 * @功能   TODO 获取点券来源分页总数
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.DqtzxiDao#gettDqtzxiCount(net.sf.json.JSONObject)  
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int gettDqtzxiCount(JSONObject params) {
		String yhm = params.optString("yhm");
		String sjhm=params.optString("sjhm");
		String ltype=params.optString("ltype");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT count(*) FROM t_zhfw_tg_dq where 1=1 ");
		if(StringUtils.isNotBlank(startDate)){
			sql.append(" AND date_format(LY_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if(StringUtils.isNotBlank(endDate)){
			sql.append(" AND date_format(LY_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if(StringUtils.isNotEmpty(yhm)){
			sql.append(" AND USER_NAME like "+ "'%"+yhm+"%'");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(ltype)){
			sql.append(" and LY_TYPE = :ltype ");
			paramMap.put("ltype", ltype);
		}
		logger.info("gettDqtzxiCount sql is " + sql.toString());
		logger.info("gettDqtzxiCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	/* 
	 * @方法名  getDqtzxiSum 
	 * @功能   TODO 获取点券来源汇总合计
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.DqtzxiDao#getDqtzxiSum(net.sf.json.JSONObject)  
	 */
	@Override
	public String getDqtzxiSum(JSONObject params) {
		String yhm = params.optString("yhm");
		String sjhm=params.optString("sjhm");
		String ltype=params.optString("ltype");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT round(sum(DQ_JE),0) DQJE FROM t_zhfw_tg_dq where 1=1 ");
		if(StringUtils.isNotBlank(startDate)){
			sql.append(" AND date_format(LY_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if(StringUtils.isNotBlank(endDate)){
			sql.append(" AND date_format(LY_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if(StringUtils.isNotEmpty(yhm)){
			sql.append(" AND USER_NAME like "+ "'%"+yhm+"%'");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(ltype)){
			sql.append(" AND LY_TYPE = :ltype ");
			paramMap.put("ltype", ltype);
		}
		logger.info("getDqtzxiSum sql is " + sql.toString());
		logger.info("getDqtzxiSum paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, String.class);
	}

}
